# -*- coding: utf-8 -*-
import pymysql
from pymysql import *

import json
import sys
import datetime
from utils.icloudp_log import IcloudpLog

logging = IcloudpLog.get_logger_db()


class MysqlHelper(object):

    # todo 类的构造函数，主要用于类的初始化
    # def __init__(self, host="127.0.0.1", port=3306, db="tracesource", user="root", pwd="#chengan2014#", charset="utf8"):
    #     self.__host = host
    #     self.__port = port
    #     self.__db = db
    #     self.__user = user
    #     self.__passwd = pwd
    #     self.__charset = charset

    # todo 类的构造函数，主要用于类的初始化
    def __init__(self, conn_params={}):
        conn_params['charset'] = conn_params['charset'] if 'charset' in conn_params.keys() else 'utf8'
        self.__host = conn_params['host']
        self.__port = int(conn_params['port'])
        self.__db = conn_params['db']
        self.__user = conn_params['user']
        self.__passwd = conn_params['passwd']
        self.__charset = conn_params['charset']

    # todo 建立数据库连接和打开游标
    def __connect(self):
        try:
            self.__conn = connect(host=self.__host,
                                  port=self.__port,
                                  db=self.__db,
                                  user=self.__user,
                                  passwd=self.__passwd,
                                  charset=self.__charset,
                                  cursorclass=pymysql.cursors.DictCursor)
            self.__cursor = self.__conn.cursor()
        except Exception as e:
            logging.error('ERROR: create {0} db connect failed on {1}.\n'.format(self.__host, datetime.datetime.now()))
            raise Exception(f"数据库连接失败，e={str(e)}")

    # todo 关闭游标和关闭连接
    def __close(self):
        self.__cursor.close()
        self.__conn.close()

    # todo 取一条数据
    def get_one(self, sql, params=()):
        result = None
        try:
            self.__connect()
            self.__cursor.execute(sql, params)
            result = self.__cursor.fetchone()
            self.__close()
        except Exception as e:
            logging.error("执行sql={0}，错误：{1}".format(sql, str(e)))
            raise Exception(f"执行sql失败，e={str(e)}")
        return result

    # todo 取所有数据
    def get_all(self, sql, params=()):
        lst = []
        try:
            self.__connect()
            self.__cursor.execute(sql, params)
            lst = self.__cursor.fetchall()
            self.__close()
        except Exception as e:
            logging.error("执行sql={0}错误：{1}".format(sql, str(e)))
            raise Exception(f"执行sql失败，e={str(e)}")
        return lst

    # todo 增加数据
    def insert(self, sql, params=()):
        return self.__edit(sql, params)

    # todo 修改数据
    def update(self, sql, params=()):
        return self.__edit(sql, params)

    # todo 修改数据
    def updates(self, sqls, params=[]):
        return self.__edits(sqls, params)

    # todo 删除数据
    def delete(self, sql, params=()):
        return self.__edit(sql, params)

    # todo 写数据操作具体实现，增删改操作都是调用这个方法来实现，这是个私有方法，不允许类外部调用
    def __edit(self, sql, params=()):
        count = 0
        try:
            self.__connect()
            count = self.__cursor.execute(sql, params)
            self.__conn.commit()
            self.__close()
        except Exception as e:
            logging.error("执行sql={0}错误：{1}".format(sql, str(e)))
            raise Exception(f"执行sql失败，e={str(e)}")
        return count

    # todo 写数据操作具体实现，增删改操作都是调用这个方法来实现，这是个私有方法，不允许类外部调用
    def __edits(self, sqls, params=[]):
        count = 0
        try:
            self.__connect()
            for i in range(len(sqls)):
                count += self.__cursor.execute(sqls[i], () if len(params) == 0 else params[i])
            self.__conn.commit()
            self.__close()
        except Exception as e:
            logging.error("执行sql={0}错误：{1}".format(sqls, str(e)))
            raise Exception(f"执行sql失败，e={str(e)}")
        return count

    def get_version(self):
        return self.get_one("select version() as version")


if __name__ == '__main__':
    print("Python")

'''
#查询数据
mysqlhelper = MysqlHelper(MysqlHelper.conn_params)
sql = "select * from auth_user where username=%s"
params = ('butler',)
result = mysqlhelper.get_all(sql,params)

sql = "insert into auth_user values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
params = (4,"666666",datetime.now(),0,"kobe","kobe","kobe","123456@126.com",0,1,datetime.now())
rowcount = mysqlhelper.insert(sql,params)

sql = "delete from auth_user where id=%s"
params = (4,)
rowcount = mysqlhelper.delete(sql,params)

sql = "update auth_user set username=%s where id=%s"
params = ('kobe1', 4)
rowcount = mysqlhelper.update(sql, params)
'''
